package persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Disciplina;
import model.FinalAocHwBd;
import model.FinalLabBd;
import model.FinalMetodos;
import model.FinalSo;

public class NotasDaoImpl implements NotasDao{

	Connection c;
	
	public NotasDaoImpl(){
		GenericDao gDao = new GenericDao();
		c = gDao.getConnectionNotasFaltas();
	}
	
	public List<FinalLabBd> consultaNotasLabBd() throws SQLException{
		List<FinalLabBd> lista = new ArrayList<FinalLabBd>();
		String sql = "select * from fn_tabelafinallabbd() order by nome";
		PreparedStatement ps = c.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()){
			FinalLabBd notas = new FinalLabBd();
			notas.setRa(rs.getString("ra"));
			notas.setNome(rs.getString("nome"));
			notas.setAv1(rs.getString("av1"));
			notas.setAv2(rs.getString("av2"));
			notas.setAv3(rs.getString("av3"));
			notas.setExame(rs.getString("exame"));
			notas.setMedia(rs.getString("media"));
			notas.setMediaFinal(rs.getString("mediafinal"));
			lista.add(notas);
		}
		rs.close();
		ps.close();
		return lista;
	}

	public List<FinalAocHwBd> consultaNotasAocHwBd(Disciplina disciplina) throws SQLException{
		List<FinalAocHwBd> lista = new ArrayList<FinalAocHwBd>();
		String sql = "select * from fn_tabelafinalaochwbd(?) order by nome";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, disciplina.getCodigo());
		ResultSet rs = ps.executeQuery();
		while (rs.next()){
			FinalAocHwBd notas = new FinalAocHwBd();
			notas.setRa(rs.getString("ra"));
			notas.setNome(rs.getString("nome"));
			notas.setP1(rs.getString("p1"));
			notas.setP2(rs.getString("p2"));
			notas.setT(rs.getString("t"));
			notas.setExame(rs.getString("exame"));
			notas.setMedia(rs.getString("media"));
			notas.setMediaFinal(rs.getString("mediafinal"));
			lista.add(notas);
		}
		rs.close();
		ps.close();
		return lista;
	}

	public List<FinalSo> consultaNotasSo(Disciplina disciplina) throws SQLException{
		List<FinalSo> lista = new ArrayList<FinalSo>();
		String sql = "select * from fn_tabelafinalso(?) order by nome";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, disciplina.getCodigo());
		ResultSet rs = ps.executeQuery();
		while (rs.next()){
			FinalSo notas = new FinalSo();
			notas.setRa(rs.getString("ra"));
			notas.setNome(rs.getString("nome"));
			notas.setP1(rs.getString("p1"));
			notas.setP2(rs.getString("p2"));
			notas.setT(rs.getString("t"));
			notas.setPreexame(rs.getString("preexame"));
			notas.setExame(rs.getString("exame"));
			notas.setMedia(rs.getString("media"));
			notas.setMediaFinal(rs.getString("mediafinal"));
			lista.add(notas);
		}
		rs.close();
		ps.close();
		return lista;
	}
	
	public List<FinalMetodos> consultaNotasMetodos() throws SQLException{
		List<FinalMetodos> lista = new ArrayList<FinalMetodos>();
		String sql = "select * from fn_tabelafinalmetodos() order by nome";
		PreparedStatement ps = c.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()){
			FinalMetodos notas = new FinalMetodos();
			notas.setRa(rs.getString("ra"));
			notas.setNome(rs.getString("nome"));
			notas.setArtigoCompleto(rs.getString("artigo_completo"));
			notas.setArtigoResumido(rs.getString("artigo_resumido"));
			notas.setExame(rs.getString("exame"));
			notas.setMedia(rs.getString("media"));
			notas.setMediaFinal(rs.getString("mediafinal"));
			lista.add(notas);
		}
		rs.close();
		ps.close();
		return lista;
	}
	
}
